package com.blacamdk.ChatDatabase;

import com.blacamdk.ChatSecret.MD5Util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import static com.blacamdk.ChatDatabase.tmp.username;

/**
 * Chat_MySQL
 *
 * @author BlacAmDK
 * @date 16-12-16
 */
public class Chat_MySQL implements ChatDatabaseInterface{
    private  static Connection connection = null;
    //private PreparedStatement statement = null;

    public Chat_MySQL()
    {
        PreparedStatement statement = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(tmp.url, username, tmp.password);//tmp为数据库信息类，请自行建立
            try {connection.prepareStatement("SELECT * FROM BC_Chat.ServerInfo;").executeQuery();}
            catch (SQLException e)
            {
                try {
                    System.out.println("未检测到数据库，正在尝试建立！");
                    initTable();
                }catch (SQLException ex)
                {
                    System.err.println("数据库初始化失败！");
                    e.printStackTrace();
                    System.exit(1);
                }
            }
            return;
        } catch (ClassNotFoundException e) {
            System.err.println("装载JDBC驱动程序失败。");
        } catch (SQLException e) {
            System.err.println("无法连接数据库！");
            e.printStackTrace();
        }
        System.exit(1);
    }
    //初始化数据库
    private void initTable() throws SQLException {
        //用户登录信息表
        String query = "CREATE DATABASE BC_Chat;";
        System.out.println("建立数据库……" + connection.prepareStatement(query).executeUpdate());
        query = "CREATE TABLE BC_Chat.user_login ( UID INTEGER UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, Username CHAR(13) NOT NULL, Password CHAR(32) NOT NULL, PKey CHAR(32), Status TINYINT UNSIGNED DEFAULT 0 NOT NULL, LastTry TIMESTAMP DEFAULT current_timestamp() NOT NULL ON UPDATE current_timestamp(), FailedCounter TINYINT UNSIGNED DEFAULT 0 NOT NULL );";
        System.out.println("建立用户登录信息表……" + connection.prepareStatement(query).executeUpdate());
        query = "CREATE UNIQUE INDEX user_login_Username_uindex ON BC_Chat.user_login (Username);";
        System.out.println("建立用户登录索引……" + connection.prepareStatement(query).executeUpdate());
        //服务器信息表
        query = "CREATE TABLE BC_Chat.ServerInfo ( Version FLOAT UNSIGNED NOT NULL, InitTime TIMESTAMP DEFAULT current_timestamp() NOT NULL, ServerKey CHAR(32) NOT NULL )";
        System.out.println("建立服务器信息表……" + connection.prepareStatement(query).executeUpdate());
        //插入Admin用户
        query = "INSERT INTO BC_Chat.user_login(Username, Password, PKey, Status) VALUES(?, ?, ?, ?);";
        PreparedStatement stat = connection.prepareStatement(query);
        stat.setString(1, "Admin");
        stat.setString(2, "11111111111111111111111111111111");
        stat.setString(3, MD5Util.MD5("qweiutqfgweuk"));
        stat.setInt(4, 1);//管理员实时在线
        System.out.println("插入Admin用户信息……" + stat.executeUpdate());
        //未接受的消息存储表
        query = "CREATE TABLE BC_Chat.unread ( UFrom INTEGER UNSIGNED NOT NULL, UTo INTEGER UNSIGNED NOT NULL, PKey CHAR(32) NOT NULL, Content TEXT, CTime TIMESTAMP NOT NULL )";
        System.out.println("建立未读信息存储表……" + connection.prepareStatement(query).executeUpdate());
    }

    /*
    //测试用主类
    public static void main(String[] args) {
        Chat_MySQL test = new Chat_MySQL();
        //System.out.println(test.register("Black", "123456789"));
        System.out.println(test.login("Black", "123456789", 1, "KEYYYYYY"));
        //System.out.println(test.send_to("KEYYYYYY", 2, 2, "asdasdasd"));
        ResultSet rs = test.receive("KEYYYYYY", 2, new Date().getTime());
        try {
            while(rs.next())
                System.out.println(rs.getString("Content"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(test.logout("KEYYYYYY", 2));

    }
*/
    //检查指定UID的用户的密钥是否正确
    //-1:无此用户
    //-2:密钥错误
    //-3:密钥为空
    //0:匹配
    private int checkKEY(String key, int uid)
    {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("SELECT PKey FROM BC_Chat.user_login WHERE UID = (?);");
            ps.setInt(1, uid);
            rs = ps.executeQuery();
            if (!rs.next())
                return -1;
            if(rs.getString("PKey") == null)
                return -3;
            if (!rs.getString("PKey").equals(key))//密钥不正确
                return -2;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (rs != null) rs.close();
                if (ps != null) ps.close();
            } catch (SQLException ignored) { }
        }
        return 0;
    }
    @Override
    public ERR_TYPE register(String username, String password) {
        String query = "INSERT INTO BC_Chat.user_login(Username, Password) VALUES(?, ?);";
        PreparedStatement stat;
        try {
            stat = connection.prepareStatement(query);
            stat.setString(1, username);
            stat.setString(2, password);
            stat.executeUpdate();
            stat.close();
        } catch (SQLException e) {
            return ERR_TYPE.USERNAME_EXIST;
        }
        return ERR_TYPE.OK;
    }

    @Override
    public ERR_TYPE login(String username, String password, int status, String key) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean flag = false;//错误次数过多后的标记，用来重置失败计数器
        try {
            ps = connection.prepareStatement("SELECT * FROM BC_Chat.user_login WHERE Username = (?);");
            ps.setString(1, username);
            rs = ps.executeQuery();
            if(!rs.next())
                return ERR_TYPE.USERNAME_NOT_EXIST;
            if(rs.getInt("FailedCounter") >= 5/* 检验登录失败次数 */) {
                if (new Date().getTime() - rs.getTimestamp("LastTry").getTime() < 60000L * 5/* 错误次数超过5次后的5分钟禁止登录 */) {
                    return ERR_TYPE.FAILED_TOO_MANY;
                }
                ps = connection.prepareStatement("UPDATE BC_Chat.user_login SET FailedCounter = (?) WHERE BC_Chat.user_login.Username = (?);");
                ps.setInt(1, 0);//失败计数器重置为0
                ps.setString(2, username);
                ps.executeUpdate();
                flag = true;
            }
            if(!password.equals(rs.getString("Password")))//密码错误
            {
                ps = connection.prepareStatement("UPDATE BC_Chat.user_login SET FailedCounter = (?) WHERE BC_Chat.user_login.Username = (?);");
                ps.setInt(1, (flag ? 0 : rs.getInt("FailedCounter")) + 1);
                ps.setString(2, username);
                ps.executeUpdate();
                return ERR_TYPE.WRONG_PASSWORD;
            }
            //登录成功
            ps = connection.prepareStatement("UPDATE BC_Chat.user_login SET Status = (?), FailedCounter = (?), PKey = (?) WHERE BC_Chat.user_login.Username = (?);");
            ps.setInt(1, status);
            ps.setInt(2, 0);
            ps.setString(3, key);
            ps.setString(4, username);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return ERR_TYPE.OTHER;
        }
        finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ERR_TYPE.OK;
    }

    @Override
    public ERR_TYPE logout(String key, int uid) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            int ret = checkKEY(key, uid);
            if(ret == -1)//无此用户
                return ERR_TYPE.USERNAME_NOT_EXIST;
            else if(ret == -2)//密钥错误
                return ERR_TYPE.WRONG_KEY;
            else {
                ps = connection.prepareStatement("UPDATE BC_Chat.user_login SET Status = 0, PKey = NULL WHERE UID = (?);");
                ps.setInt(1, uid);
                ps.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return ERR_TYPE.OTHER;
        }finally {
            try {
                if (ps != null) ps.close();
            } catch (SQLException ignored) { }
        }
        return ERR_TYPE.OK;
    }

    //CTime:发送时间戳
    @Override
    public long send_to(String key, int from, int to, String content) {
        PreparedStatement ps;
        long CTime = checkKEY(key, from);
        try {
            if(CTime < 0)//错误
                return CTime;
            ps = connection.prepareStatement("INSERT INTO BC_Chat.unread(UFrom, UTo, PKey, Content, CTime) VALUES (?, ?, ?, ?, ?);");
            ps.setInt(1, from);
            ps.setInt(2, to);
            ps.setString(3, key);
            ps.setString(4, content);
            CTime = new Date().getTime();
            ps.setTimestamp(5, new Timestamp(CTime));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -10L;
        }

        return CTime;
    }

    @Override
    public ResultSet receive(String key, int uid, long time) {
        PreparedStatement ps;
        ResultSet rs = null;
        if(checkKEY(key, uid) == 0)//认证通过
            try {
                long DTime = new Date().getTime();
                ps = connection.prepareStatement("SELECT UFrom, Content, CTime FROM BC_Chat.unread WHERE CTime > (?) AND CTime < (?) AND UTo = (?);");
                ps.setTimestamp(1, new Timestamp(time));
                ps.setTimestamp(2, new Timestamp(DTime));
                ps.setInt(3, uid);
                rs = ps.executeQuery();
                if(!rs.next())
                    return null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        return rs;
    }
    public int getUID(String username)
    {
        PreparedStatement ps;
        try {
            ps = connection.prepareStatement("SELECT UID FROM BC_Chat.user_login WHERE Username = (?);");
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            if(rs.next())
                return rs.getInt("UID");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }
    public ResultSet getUserList(String key, int uid) {
        PreparedStatement ps;
        ResultSet rs = null;
        if(checkKEY(key, uid) == 0)//认证通过
            try {
                ps = connection.prepareStatement("SELECT UID, Username FROM BC_Chat.user_login WHERE Status = 1;");
                rs = ps.executeQuery();
                if(!rs.next())
                    return null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        return rs;
    }
}
